from tornado.web import authenticated
from sqlalchemy import cast, DATE, extract, and_
from common.BaseHandler import BaseHandler
from common.Models import Post, Tag, Category, Comment, PostViewHistory, PostUpdateHistory, VisitorLogs
from utils.DateHandler import DateHandler


class IndexHandler(BaseHandler):
    @authenticated
    def get(self):
        if "api" in self.request.uri:
            result = self.result()
            try:
                over_7_days_data, today_data, yesterday_data, this_month_data, last_month_data, total_data = {}, {}, {}, {}, {}, {}
                # 过去7日数据
                # 网站访问数据
                sql = """select a.click_date,ifnull(b.ips,0) as ips
                            from (
                                SELECT curdate() as click_date
                                union all
                                SELECT date_sub(curdate(), interval 1 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 2 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 3 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 4 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 5 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 6 day) as click_date
                            ) a left join (
                              select date(create_time) as datetime, count(DISTINCT ip_address) AS 'ips'
                              from visitor_logs
                              group by date(create_time)
                            ) b on a.click_date = b.datetime;"""
                website_visit_data = self.session.execute(sql).fetchall()
                # print(website_visit_data)
                over_7_days_data["website_visit_data"] = [{"date": str(i[0])[5:], "ips": i[1]} for i in website_visit_data][::-1]

                # 博客阅读数据
                sql = """select a.click_date,ifnull(b.count,0) as 'count'
                            from (
                                SELECT curdate() as click_date
                                union all
                                SELECT date_sub(curdate(), interval 1 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 2 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 3 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 4 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 5 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 6 day) as click_date
                            ) a left join (
                              select date(create_time) as datetime, count(*) AS 'count'
                              from post_view_history
                              group by date(create_time)
                            ) b on a.click_date = b.datetime;"""
                blog_visit_data = self.session.execute(sql).fetchall()
                # print(blog_visit_data)
                over_7_days_data["blog_visit_data"] = [{"date": str(i[0]), "count": i[1]} for i in blog_visit_data][::-1]

                # 博客评论数据
                sql = """select a.click_date,ifnull(b.count,0) as count
                            from (
                                SELECT curdate() as click_date
                                union all
                                SELECT date_sub(curdate(), interval 1 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 2 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 3 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 4 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 5 day) as click_date
                                union all
                                SELECT date_sub(curdate(), interval 6 day) as click_date
                            ) a left join (
                              select date(create_time) as datetime, count(*) AS 'count'
                              from comment
                              group by date(create_time)
                            ) b on a.click_date = b.datetime;"""
                comment_data = self.session.execute(sql).fetchall()
                # print(comment_data)
                over_7_days_data["comment_data"] = [{"date": str(i[0]), "count": i[1]} for i in comment_data][::-1]

                # 今日数据
                today_data["date"] = DateHandler.today()
                # 网站访问量
                today_data["website_visit_count"] = self.session.query(VisitorLogs).filter(cast(VisitorLogs.create_time, DATE) == DateHandler.today()).count()
                # 博文浏览量
                today_data["blog_visit_count"] = self.session.query(PostViewHistory).filter(cast(PostViewHistory.create_time, DATE) == DateHandler.today()).count()
                # 博文评论量
                today_data["blog_comment_count"] = self.session.query(Comment).filter(cast(Comment.create_time, DATE) == DateHandler.today()).count()
                # 新增博文
                today_data["new_blog_count"] = self.session.query(Post).filter(cast(Post.create_time, DATE) == DateHandler.today()).count()
                # 修改博文
                today_data["update_blog_count"] = self.session.query(PostUpdateHistory).filter(cast(PostUpdateHistory.create_time, DATE) == DateHandler.today()).count()
                # 新增分类
                today_data["new_category_count"] = self.session.query(Category).filter(cast(Category.create_time, DATE) == DateHandler.today()).count()
                # 新增标签
                today_data["new_tag_count"] = self.session.query(Tag).filter(cast(Tag.create_time, DATE) == DateHandler.today()).count()

                # 昨日数据
                yesterday_data["date"] = DateHandler.get_last_day()
                # 网站访问量
                yesterday_data["website_visit_count"] = self.session.query(VisitorLogs).filter(cast(VisitorLogs.create_time, DATE) == DateHandler.get_last_day()).count()
                # 博文浏览量
                yesterday_data["blog_visit_count"] = self.session.query(PostViewHistory).filter(cast(PostViewHistory.create_time, DATE) == DateHandler.get_last_day()).count()
                # 博文评论量
                yesterday_data["blog_comment_count"] = self.session.query(Comment).filter(cast(Comment.create_time, DATE) == DateHandler.get_last_day()).count()
                # 新增博文
                yesterday_data["new_blog_count"] = self.session.query(Post).filter(cast(Post.create_time, DATE) == DateHandler.get_last_day()).count()
                # 修改博文
                yesterday_data["update_blog_count"] = self.session.query(PostUpdateHistory).filter(cast(PostUpdateHistory.create_time, DATE) == DateHandler.get_last_day()).count()
                # 新增分类
                yesterday_data["new_category_count"] = self.session.query(Category).filter(cast(Category.create_time, DATE) == DateHandler.get_last_day()).count()
                # 新增标签
                yesterday_data["new_tag_count"] = self.session.query(Tag).filter(cast(Tag.create_time, DATE) == DateHandler.get_last_day()).count()

                # 本月数据
                this_month = DateHandler.today().split("-")
                this_month_data["date"] = f"{this_month[0]}-{this_month[1]}"
                # 网站访问量
                this_month_data["website_visit_count"] = self.session.query(VisitorLogs).filter(and_(extract('year', VisitorLogs.create_time) == int(this_month[0]), extract('month', VisitorLogs.create_time) == int(this_month[1]))).count()
                # 博文浏览量
                this_month_data["blog_visit_count"] = self.session.query(PostViewHistory).filter(and_(extract('year', PostViewHistory.create_time) == int(this_month[0]), extract('month', PostViewHistory.create_time) == int(this_month[1]))).count()
                # 博文评论量
                this_month_data["blog_comment_count"] = self.session.query(Comment).filter(and_(extract('year', Comment.create_time) == int(this_month[0]), extract('month', Comment.create_time) == int(this_month[1]))).count()
                # 新增博文
                this_month_data["new_blog_count"] = self.session.query(Post).filter(and_(extract('year', Post.create_time) == int(this_month[0]), extract('month', Post.create_time) == int(this_month[1]))).count()
                # 修改博文
                this_month_data["update_blog_count"] = self.session.query(PostUpdateHistory).filter(and_(extract('year', PostUpdateHistory.create_time) == int(this_month[0]), extract('month', PostUpdateHistory.create_time) == int(this_month[1]))).count()
                # 新增分类
                this_month_data["new_category_count"] = self.session.query(Category).filter(and_(extract('year', Category.create_time) == int(this_month[0]), extract('month', Category.create_time) == int(this_month[1]))).count()
                # 新增标签
                this_month_data["new_tag_count"] = self.session.query(Tag).filter(and_(extract('year', Tag.create_time) == int(this_month[0]), extract('month', Tag.create_time) == int(this_month[1]))).count()

                # 上月数据
                month_start, month_end = DateHandler().get_last_month_start_end(DateHandler.today())
                this_month = month_start.split("-")
                last_month_data["date"] = f"{this_month[0]}-{this_month[1]}"
                # 网站访问量
                last_month_data["website_visit_count"] = self.session.query(VisitorLogs).filter(and_(extract('year', VisitorLogs.create_time) == int(this_month[0]), extract('month', VisitorLogs.create_time) == int(this_month[1]))).count()
                # 博文浏览量
                last_month_data["blog_visit_count"] = self.session.query(PostViewHistory).filter(and_(extract('year', PostViewHistory.create_time) == int(this_month[0]), extract('month', PostViewHistory.create_time) == int(this_month[1]))).count()
                # 博文评论量
                last_month_data["blog_comment_count"] = self.session.query(Comment).filter(and_(extract('year', Comment.create_time) == int(this_month[0]), extract('month', Comment.create_time) == int(this_month[1]))).count()
                # 新增博文
                last_month_data["new_blog_count"] = self.session.query(Post).filter(and_(extract('year', Post.create_time) == int(this_month[0]), extract('month', Post.create_time) == int(this_month[1]))).count()
                # 修改博文
                last_month_data["update_blog_count"] = self.session.query(PostUpdateHistory).filter(and_(extract('year', PostUpdateHistory.create_time) == int(this_month[0]), extract('month', PostUpdateHistory.create_time) == int(this_month[1]))).count()
                # 新增分类
                last_month_data["new_category_count"] = self.session.query(Category).filter(and_(extract('year', Category.create_time) == int(this_month[0]), extract('month', Category.create_time) == int(this_month[1]))).count()
                # 新增标签
                last_month_data["new_tag_count"] = self.session.query(Tag).filter(and_(extract('year', Tag.create_time) == int(this_month[0]), extract('month', Tag.create_time) == int(this_month[1]))).count()

                # 累计数据
                # 网站访问量
                total_data["website_visit_count"] = self.session.query(VisitorLogs).count()
                # 博文浏览量
                total_data["blog_visit_count"] = self.session.query(PostViewHistory).count()
                # 博文评论量
                total_data["blog_comment_count"] = self.session.query(Comment).filter_by(is_delete=0).count()
                # 博文
                total_data["blog_count"] = self.session.query(Post).filter_by(is_delete=0).count()
                # 修改博文
                total_data["update_blog_count"] = self.session.query(PostUpdateHistory).count()
                # 分类
                total_data["category_count"] = self.session.query(Category).filter_by(is_delete=0).count()
                # 标签
                total_data["tag_count"] = self.session.query(Tag).filter_by(is_delete=0).count()

                result["status"] = 1
                result["msg"] = "数据获取成功！"
                result["data"] = {
                    "over_7_days_data": over_7_days_data,
                    "today_data": today_data,
                    "yesterday_data": yesterday_data,
                    "this_month_data": this_month_data,
                    "last_month_data": last_month_data,
                    "total_data": total_data
                }
            except Exception as e:
                self.session.rollback()
                self.logger.error(e)
                result = self.check_except_zh(e, msg="数据获取失败！")
            self.write(result)
        else:
            self.render("management/index.html")

